package news;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.lang.model.element.NestingKind;
import javax.xml.bind.DataBindingException;

import common.DBUtil;
import user.User;

/**
 * 用户业务处理类(功能类)
 * @author 谢一弘
 * @param <QueryRunner>
 * @date 2020-5-8
 */
public class Newsdao {

	private News news;// 用户信息
	private DBUtil db = new DBUtil();
	public Newsdao() {

	}
	public  Newsdao(News news) {
       this.news= news;
	}
	public News getNews() {
		return news;
	}
	public void setNews(News news) {
		this.news = news;
	}
	/**
	 * 添加新闻
	 * @return >=0 成功 -1 失败
	 */
	public int doNewsAdd(News news) {
		db = new DBUtil();
		int result = -1;
		String sql = "insert into news(title,content,uid,pubtime)";
		sql += "values('" + news.getTitle() + "','" + news.getContent() + "','"+ news.getUid() + "','"+news.getPubtime()+"')";
		result = db.executeUpdate(sql);
		db.closeDB();
		return result;
		
	}
	/**
	 * 添加新闻
	 * @return >=0 成功 -1 失败
	 */
	public int doNewsAdd() {
		db = new DBUtil();
		int result = -1;
		String sql = "insert into news(title,content,uid)";
		sql += "values('" + news.getTitle() + "','" + news.getContent() + "','"+ news.getUid() + "')";
		result = db.executeUpdate(sql);
		db.closeDB();
		return result;
		
	}
	/**
	 * 根据新闻编号得到完整的新闻信息
	 * @param nid 新闻编号
	 * @return 新闻列表
	 */
	public ArrayList<News> doNewsQuery(int nid){
		db = new DBUtil();
		ArrayList<News> list = new ArrayList<News>();
		String sql ="select nid,title,uname,content,news.uid,pubtime,isValid from news left JOIN user on news.uid = user.uid where nid ='"+nid+"';";
		ResultSet resultSet = db.executeQuery(sql);
		try {
			while(resultSet.next()) {
				News news = new News();
			  news.setNid(resultSet.getInt("nid"));
			  news.setTitle(resultSet.getString("title"));
			  news.setUname(resultSet.getString("uname"));
			  news.setContent(resultSet.getString("content"));
              news.setPubtime(resultSet.getTimestamp("pubtime"));
              news.setIsValid(resultSet.getInt("isValid"));
              list.add(news);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 根据新闻标题得到完整的新闻信息
	 * @param title
	 * @return
	 */
	public ArrayList<News> doNewsQuery(String title){	
		db = new DBUtil();
		ArrayList<News> list = new ArrayList<News>();
		String sql ="select nid,title,uname,content,news.uid,pubtime,isValid from news left JOIN user on news.uid = user.uid where title ='"+title+"';";
		ResultSet resultSet = db.executeQuery(sql);
		try {
			while(resultSet.next()) {
				News news = new News();
			  news.setNid(resultSet.getInt("nid"));
			  news.setTitle(resultSet.getString("title"));
			  news.setUname(resultSet.getString("uname"));
			  news.setContent(resultSet.getString("content"));
			  news.setUid(resultSet.getInt("uid"));
              news.setPubtime(resultSet.getTimestamp("pubtime"));
              news.setIsValid(resultSet.getInt("isValid"));
              news.setUid(resultSet.getInt("uid"));
              list.add(news);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
		
	}
	/**
	 * 查看新闻
	 * @return 新闻列表
	 */
	public ArrayList<News> doNewsQuery(){
		db = new DBUtil();
		ArrayList<News> list = new ArrayList<News>();
		String sql ="select nid,title,uname,content,news.uid,pubtime,isValid,isTop from news left JOIN user on news.uid = user.uid;";
		ResultSet resultSet = db.executeQuery(sql);
		try {
			while(resultSet.next()) {
				News news = new News();
			  news.setNid(resultSet.getInt("nid"));
			  news.setTitle(resultSet.getString("title"));
			  news.setUname(resultSet.getString("uname"));
			  news.setContent(resultSet.getString("content"));
			  news.setUid(resultSet.getInt("uid"));
              news.setPubtime(resultSet.getTimestamp("pubtime"));
              news.setIsValid(resultSet.getInt("isValid"));
              list.add(news);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
		
	}
	/**
	 * 根据新闻编号得到完整的新闻信息
	 * @param nid
	 * @return 新闻列表
	 */
	public News doNewsQueryById(int nid) {
		db = new DBUtil();
		News news = null;
		String sql ="select nid,title,uname,content,pubtime,isValid,isTop from news left JOIN user on news.uid = user.uid where nid ='"+nid+"';";
		ResultSet resultSet = db.executeQuery(sql);
		try {
			if(resultSet.next()) {
				 news = new News();
				  news.setNid(resultSet.getInt("nid"));
				  news.setTitle(resultSet.getString("title"));
				  news.setUname(resultSet.getString("uname"));
				  news.setContent(resultSet.getString("content"));
	              news.setPubtime(resultSet.getTimestamp("pubtime"));
	              news.setIsValid(resultSet.getInt("isValid"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return news;
	}
	/**
	 * 根据新闻标题得到完整的新闻信息
	 * @param nid
	 * @return 新闻列表
	 */
	public News doNewsQueryByTitle(String title) {
		db = new DBUtil();
		News news = null;
		String sql ="select nid,title,uname,content,news.uid,pubtime,isValid,isTop from news left JOIN user on news.uid = user.uid where title like '%"+title+"%' and uid ='"+news.getUid()+"';";
		ResultSet resultSet = db.executeQuery(sql);
		try {
			if(resultSet.next()) {
				 news = new News();
				  news.setNid(resultSet.getInt("nid"));
				  news.setTitle(resultSet.getString("title"));
				  news.setUname(resultSet.getString("uname"));
				  news.setContent(resultSet.getString("content"));
	              news.setPubtime(resultSet.getTimestamp("pubtime"));
	              news.setIsValid(resultSet.getInt("isValid"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return news;
	}
	/**
	 * 根据新闻标题得到完整的新闻信息
	 * @param nid
	 * @return 新闻列表
	 */
	
	/**
	 * 显示新闻
	 * @return 新闻
	 */
	public ArrayList<News> doNewsQueryByTitle() {
		db = new DBUtil();
		ArrayList<News> list = new ArrayList<News>();
		String sql ="select nid,title,uname,content,pubtime,isValid1,isTop from news left JOIN user on news.uid = user.uid ;";
		ResultSet resultSet = db.executeQuery(sql);
		try {
			while(resultSet.next()) {
				  news = new News();
				  news.setNid(resultSet.getInt("nid"));
				  news.setTitle(resultSet.getString("title"));
				  news.setUname(resultSet.getString("uname"));
				  news.setContent(resultSet.getString("content"));
	              news.setPubtime(resultSet.getTimestamp("pubtime"));
	              news.setIsValid(resultSet.getInt("isValid1"));
	              news.setIsTop(resultSet.getInt("isTop"));
	              list.add(news);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 显示新闻
	 * @return 新闻
	 */
	public News doNewsQueryById() {
		db = new DBUtil();
		News news = null;
		String sql ="select nid,title,uname,content,uid,pubtime,isValid,isTop from news left JOIN user on news.uid = user.uid where nid= '"+news.getNid()+"';";
		ResultSet resultSet = db.executeQuery(sql);
		try {
			while(resultSet.next()) {
				  news = new News();
				  news.setNid(resultSet.getInt("nid"));
				  news.setTitle(resultSet.getString("title"));
				  news.setUname(resultSet.getString("uname"));
				  news.setContent(resultSet.getString("content"));
				  news.setUid(resultSet.getInt("uid"));
	              news.setPubtime(resultSet.getTimestamp("pubtime"));
	              news.setIsValid(resultSet.getInt("isValid"));
	             
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return news;
	}
	/**
	 * 删除新闻
	 * @param nid
	 * @return -1-失败 >=0 成功
	 */
	public int doDeleteNewsByID(int nid) {
		DBUtil db = new DBUtil();
		
		int result = -1;
		String sql = "delete from news where nid=" + nid + "";
		result = db.executeUpdate(sql);
		db.closeDB();
		return result;
	}
	/**
	 * 删除新闻
	 * @param uname  新闻标题
	 * @return -1-失败 >=0 成功
	 */
	public int doDeleteNewsByID(String title) {
		int result = -1;
		String sql = "delete from news where title=" + title + "";
		result = db.executeUpdate(sql);
		db.closeDB();
		return result;
	}
	/**
	 * 删除新闻
	 * @param news
	 * @return -1-失败 >=0 成功
	 */
	public int doDeleteNewsByID(News news) {
		db = new DBUtil();
		int result = -1;
		String sql = "delete from news where nid=" + news.getNid() + "";
		result = db.executeUpdate(sql);
		db.closeDB();
		return result;
	}
	/**
	 * 删除新闻
	 * @return -1-失败 >=0 成功
	 */
	public int doDeleteNewsByID() {
		db = new DBUtil();
		int result = -1;
		String sql = "delete from news where nid=" + news.getNid() + "";
		result = db.executeUpdate(sql);
		db.closeDB();
		return result;
	}
	/**
	 * 根据新闻编号修改新闻
	 * @param nid
	 * @return -1-失败 >=0 成功
	 */
	public  int doNewsUpdate(int nid,String content) {
		db = new DBUtil();
		int result = -1;
		String sql ="update news set content ='"+content+"'  where nid ='"+nid+"';";
		result = db.executeUpdate(sql);
		return result;
		
	}
	/**
	 * 审核
	 * @param nid
	 * @return
	 */
	public int doNewsUpdateisValid(int nid) {
		int isValid = 0;
		int isValid_1;
		String sql = "select isValid1 from news where  nid='" + nid + "'";
		ResultSet resultSet = db.executeQuery(sql);
		try {
			if (resultSet.next()) {
				isValid = resultSet.getInt("isValid");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//如果该用户是普通用户，更改后为管理员
		if (isValid == 0) {
			isValid_1 = 1;
		} else {
			isValid_1 = 0;
		}
		// System.out.println(type_1);
		String sql_1 = "update news set isValid1  ='" + isValid_1 + "'  where nid ='" + nid + "';";
		int result = db.executeUpdate(sql_1);
		return result;
		
	}
	public ArrayList<News> doNewsPage(int pages , int limit) {
		ArrayList<News> list = new ArrayList<News>();
		db = new DBUtil();
		String sql = "select nid,title,uname,content,pubtime,isValid1,isTop from news left JOIN user on news.uid = user.uid order by nid limit " + (pages - 1) * limit + "," + limit+";";
		ResultSet resultSet = db.executeQuery(sql);
		try {
			while(resultSet.next()) {
				  news = new News();
				  news.setNid(resultSet.getInt("nid"));
				  news.setTitle(resultSet.getString("title"));
				  news.setUname(resultSet.getString("uname"));
				  news.setContent(resultSet.getString("content"));
	              news.setPubtime(resultSet.getTimestamp("pubtime"));
	              news.setIsValid(resultSet.getInt("isValid1"));
	              news.setIsTop(resultSet.getInt("isTop"));
	              list.add(news);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
		}
}
